from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

app = Flask(__name__)

HOSTNAME = "127.0.0.1"
USERNAME = "root"
PASSWORD = "root"
PORT = 3306
DATABASE = "flask_database"

app.config[
    "SQLALCHEMY_DATABASE_URI"] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"

db = SQLAlchemy(app)

with app.app_context():
    with db.engine.connect() as conn:
        rs = conn.execute(text("select 1"))
        print(rs.fetchone())


class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(100), nullable=False)
    address = db.Column(db.String(255), nullable=False)
    # age = db.Column(db.Integer, nullable=False)


with app.app_context():
    db.create_all()


@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'


@app.route("/user/add")
def user_add():
    # 创建ORM对象
    user = User(username="老刘", address="吉林省吉林市")
    # 将ORM对象添加到db.session中
    db.session.add(user)
    # 将db.session中的改变同步到数据库中去
    db.session.commit()
    return "<h1>添加用户成功</h1>"


@app.route("/user/query")
def user_query():
    # 1.get查找,根据主键查找
    # res = User.query.get(1)
    # 处理成字典列表
    """ 字典推导式理解过程
     for key, value in res.__dict__.items():
         if not key.startswith('_'):
             print(key, value)
    """
    # user_data = [{key: value for key, value in res.__dict__.items() if not key.startswith("_")}]
    # print(user_data)

    # 2.filter_by查找
    users = User.query.filter_by(username="zhangsan")
    # user_data = []
    #
    # for user in users:
    #     item = {key: value for key, value in user.__dict__.items() if not key.startswith("_")}
    #     user_data.append(item)
    user_data = [{c.name: getattr(user, c.name) for c in user.__table__.columns} for user in users]
    print(user_data)
    return f"<h1>查找成功</h1>"


@app.route("/user/edit")
def user_edit():
    user = User.query.get(1)
    user.address = "四川省成都市新都区"
    db.session.commit()
    return "<h1>修改成功</h1>"


@app.route("/user/delete")
def user_delete():
    # 先查找
    user = User.query.get(3)
    # 从db.session中删除
    db.session.delete(user)
    # 提交
    db.session.commit()
    return "<h1>删除成功</h1>"



if __name__ == '__main__':
    app.run()
